SET NOCOUNT ON
USE tempdb
go
-- Create a databases to work in.
CREATE DATABASE PermTest
ALTER AUTHORIZATION ON DATABASE::PermTest TO sa

-- Create a test user
CREATE LOGIN TestUser WITH PASSWORD = N'Radio taxi'
go

USE PermTest
go
-- Create a user for TestUser and a role with TestUser as member.
CREATE USER TestUser
CREATE ROLE RocknRole
ALTER ROLE RocknRole ADD MEMBER TestUser
go
-- This is a helper view to view token and user information.
CREATE VIEW tokeninfo AS
   SELECT name AS token_name, type, usage,
          original_login() AS original_login,
          SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER
   FROM   sys.user_token
go
-- Create a table to play with.
CREATE TABLE PermTable
    (id        int          NOT NULL PRIMARY KEY,
     title     nvarchar(40) NOT NULL,
     enteredby sysname      NOT NULL DEFAULT USER)
INSERT PermTable (id, title)
   VALUES (1, N'Jest taki samotny dom')
go
-- Create a stored procedure that accesses the table.
CREATE PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   INSERT PermTable (id, title) VALUES (@id, @title)
   SELECT id, title, enteredby FROM PermTable ORDER BY id
go
-- Grant RocknRole permission to run the procedure.
GRANT EXECUTE ON TestSP TO RocknRole
go
-- Impersonate the test user to test permissions. This is a nice technique to test
-- permissions which saves you from logging in separately.
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 2, N'Twoje radio'
go
-- Be ourselves again.
REVERT
go

-- Change the ownership of the table to a loginless user.
CREATE USER DifferentOwner WITHOUT LOGIN
ALTER AUTHORIZATION ON PermTable TO DifferentOwner
go
-- Test again if TestUser can run procedure.
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 3, N'Głodny'  -- Fails twice with permisssion error.
go
REVERT
go

-- Restore ownership to dbo and verify that TestUser again has access.
ALTER AUTHORIZATION ON PermTable TO dbo
go
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 3, N'Głodny'
go
REVERT
go

-- Modify the procedure to use some (bad) dynamic SQL.
ALTER PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   INSERT PermTable (id, title) VALUES (@id, @title)
   SELECT id, title, enteredby FROM PermTable
   PRINT 'The dynamic part'
   EXEC('SELECT id, title, enteredby
         FROM   PermTable
         WHERE  title = N''' + @title + '''')
go
-- Can user still run procedure?
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 4, N'Pieśń niepokorna'  -- The dynamic SQL errors out, the rest successds.
go
REVERT
go

-- Add option to reset table.
ALTER PROCEDURE TestSP @id int, @title nvarchar(40), @reset bit = 0 AS
   SELECT * FROM tokeninfo
   IF @reset = 1
   BEGIN
      TRUNCATE TABLE PermTable
      PRINT 'Truncation done'
   END
   INSERT PermTable (id, title) VALUES (@id, @title)
   SELECT id, title, enteredby FROM PermTable
go

-- Test this new version.
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 5, N'Świat od zaraz', @reset = 1
-- TRUNCATE TABLE Fails, and procedure is aborted.
go
REVERT
go

-- Test metadata access.
ALTER PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   IF object_id('PermTable') IS NOT NULL
   BEGIN
      INSERT PermTable (id, title) VALUES (@id, @title)
      SELECT id, title, enteredby FROM PermTable
   END
   ELSE
      PRINT N'No table to day'
go
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 6, N'Ratujmy co się da'  -- According to object_id, table does not exist.
go
REVERT
go
